﻿using System;
using System.Data;
using System.Data.SqlClient;

/// <summary>
///SQLServerHelper 的摘要说明
/// </summary>
public class SQLServerHelper
{
    /// <summary>
    /// 参数准备
    /// </summary>
    /// <param name="sqlCnn">数据库连接</param>
    /// <param name="sqlCmd">Command对象</param>
    /// <param name="cmdTran">事务对象</param>
    /// <param name="cmdType">Command对象类型</param>
    /// <param name="cmdText">执行的SQL语句或者存储过程</param>
    /// <param name="cmdParms">参数列表</param> 
    private static void PrepareCommand(SqlConnection sqlCnn, SqlCommand sqlCmd, SqlTransaction cmdTran, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
    {
        //判断数据库连接状态
        if (sqlCnn.State != ConnectionState.Open)
            sqlCnn.Open();
        //设置Command对象属性
        sqlCmd.Connection = sqlCnn;
        if (cmdTran != null)
            sqlCmd.Transaction = cmdTran;//判断是否要起事务
        sqlCmd.CommandType = cmdType;
        sqlCmd.CommandText = cmdText;
        if (cmdParms != null)
        {
            foreach (SqlParameter sqlParm in cmdParms)
                sqlCmd.Parameters.Add(sqlParm);
        }//判断是否有参数
    }

    /// <summary>
    /// 执行不返回结果的Sql语句，执行后返回受影响的行数（SQL语句和存储过程都可以）
    /// </summary>
    /// <remarks>
    /// 设置一个参数示例： 
    /// SqlParameter sqlParm = new SqlParameter("@X",SqlDbType.VarChar,50);
    /// sqlParm.Value = "东京";
    /// 设置SQL语句
    /// string cmdText = "select * from mdb000 where id = @X";
    /// </remarks>
    /// <param name="sqlCnn">数据库连接</param>
    /// <param name="sqlCmd">Command对象</param>
    /// <param name="cmdTran">事务对象</param>
    /// <param name="cmdType">Command对象类型</param>
    /// <param name="cmdText">执行的SQL语句或者存储过程</param>
    /// <param name="cmdParms">参数列表</param> 
    /// <returns>返回执行后影响的行数(-1:执行失败)</returns>
    public static int ExecuteNonQuery(SqlConnection sqlCnn, SqlCommand sqlCmd, SqlTransaction cmdTran, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
    {
        try
        {
            PrepareCommand(sqlCnn, sqlCmd, cmdTran, cmdType, cmdText, cmdParms);
            int iVal = sqlCmd.ExecuteNonQuery();
            sqlCmd.Parameters.Clear();
            return iVal;
        }
        catch(Exception ex)
        {
            LogHelpter.AddLog("执行SQL异常错误："+ex.Message);
            return -1;
        }
    }

    /// <summary>
    /// 执行只返回第一行第一列（适合于执行统计查询）
    /// </summary>
    /// <param name="sqlCnn">数据库连接</param>
    /// <param name="sqlCmd">Command对象</param>
    /// <param name="cmdTran">事务对象</param>
    /// <param name="cmdText">执行的SQL语句</param>
    /// <param name="cmdParms">参数列表</param> 
    /// <returns>返回一个object类型的数据</returns>
    public static object ExecuteScalar(SqlConnection sqlCnn, SqlCommand sqlCmd, SqlTransaction cmdTran, string cmdText, SqlParameter[] cmdParms)
    {
        PrepareCommand(sqlCnn, sqlCmd, cmdTran, CommandType.Text, cmdText, cmdParms);
        object oVal = sqlCmd.ExecuteScalar();
        sqlCmd.Parameters.Clear();
        return oVal;
    }

    /// <summary>
    /// 执行返回DataSet数据集（Table不能与WebService通讯，故不用）
    /// </summary>
    /// <param name="sqlCnn">数据库连接</param>
    /// <param name="sqlCmd">Command对象</param>
    /// <param name="cmdTran">事务对象</param>
    /// <param name="cmdType">类型区分是SQL语句还是存储过程</param>
    /// <param name="cmdText">执行的SQL语句或者存储过程名</param>
    /// <param name="cmdParms">参数列表</param> 
    /// <returns>DataSet数据集</returns>
    public static DataSet ExecuteDataSet(SqlConnection sqlCnn, SqlCommand sqlCmd, SqlTransaction cmdTran, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
    {
        try
        {
            PrepareCommand(sqlCnn, sqlCmd, cmdTran, cmdType, cmdText, cmdParms);
            //创建SqlDataAdapter对象
            SqlDataAdapter sqlDA = new SqlDataAdapter(sqlCmd);
            //创建DataSet对象
            DataSet ds = new DataSet();
            //填充DataSet
            sqlDA.Fill(ds);
            sqlCmd.Parameters.Clear();
            return ds;
        }
        catch
        {
            return null;
        }
    }

    /// <summary>
    /// 关闭数据库连接
    /// </summary>
    public static void closeConnection(SqlConnection sqlCnn)
    {
        if (sqlCnn.State != ConnectionState.Closed)
            sqlCnn.Close();
    }
}
